﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM; 
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class AbsenceDAL : IAbsenceDAL
    {
        private IDBManager dbManager;

        public AbsenceDAL()
        {
            DataProvider dataProvider;
            string conString;
            dataProvider = SystemInfo._DataProvider;
            conString = SystemInfo._ConnectionString;
            dbManager = new DBManager(dataProvider, conString);
        }

        public IList<hrm_ps_absence> GetAbsence(string working_date, string emp_seq)
        {
            List<hrm_ps_absence> result = new List<hrm_ps_absence>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                dbManager.AddParameters(2, "@WorkingDate", working_date);

                //strSQL = "SELECT absence_seq,absence_type_seq,emp_seq,start_date,start_time,end_date,end_time"
                strSQL = "SELECT *"
                         + " FROM HRM_PS_ABSENCE"
                         + " WHERE RECORD_STATUS = @RecordStatus";
                if (!String.IsNullOrEmpty(emp_seq)) strSQL += " AND emp_seq = @EmpSeq";
                if (!String.IsNullOrEmpty(working_date)) strSQL += " AND (start_date <= @WorkingDate AND end_date >= @WorkingDate)";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<hrm_ps_absence>) CollectionHelper.ConvertTo<hrm_ps_absence>(dtb);

                return result;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public Decimal GetSumAbsence(string record_status, string bgm_year, string month, string emp_seq)
        {
            Decimal result = 0;
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                if (record_status == null) dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                else dbManager.AddParameters(0, "@RecordStatus", record_status);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@month", month);

                strSQL = "SELECT Sum(day_count) as day_count"
                         + " FROM HRM_PS_ABSENCE"
                         + " WHERE RECORD_STATUS = @RecordStatus";
                if (!String.IsNullOrEmpty(emp_seq)) strSQL += " AND emp_seq = @EmpSeq";
                if (!String.IsNullOrEmpty(month)) strSQL += " AND MONTH(start_date) = @month";
                if (!String.IsNullOrEmpty(bgm_year)) strSQL += " AND bgm_year = @bgm_year";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = rdr.IsDBNull(7) ? 0 : rdr.GetDecimal(7);
                }
                // DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                // result = (List<hrm_ps_absence>)CollectionHelper.ConvertTo<hrm_ps_absence>(dtb);

                return result;
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<hrm_ps_absence> GetAllAbsence(string bgm_year, string month, string emp_seq)
        {
            IList<hrm_ps_absence> result = new List<hrm_ps_absence>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@month", month);

                strSQL = "SELECT *"
                         + " FROM HRM_PS_ABSENCE"
                         + " WHERE RECORD_STATUS = @RecordStatus";
                if (!String.IsNullOrEmpty(emp_seq)) strSQL += " AND emp_seq = @EmpSeq";
                if (!String.IsNullOrEmpty(month)) strSQL += " AND MONTH(start_date) = @month";
                if (!String.IsNullOrEmpty(bgm_year)) strSQL += " AND bgm_year = @bgm_year";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<hrm_ps_absence>) CollectionHelper.ConvertTo<hrm_ps_absence>(dtb);

                return result;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<AbsenceInfo> ListAbsences(AbsenceInfo abInfo)
        {
            string strSQL;
            int EmpSequence = 0;
            if (abInfo.Employee != null)
                EmpSequence = abInfo.Employee.Sequence;

            List<AbsenceInfo> infoList = new List<AbsenceInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", abInfo.Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);

                strSQL = "SELECT a.ABSENCE_SEQ, a.ABSENCE_TYPE_SEQ, a.EMP_SEQ, a.START_DATE, a.START_TIME"
                         + ", a.END_DATE, a.END_TIME, a.COUNT_DATE, a.CONTACT_ADDRESS"
                         + ", a.APPROVED_STATUS, a.APPROVED_BY, a.APPROVED_POSITION, a.APPROVED_DATE"
                         + ", a.REMARKS, a.RECORD_STATUS, a.CREATED_BY, a.UPDATED_BY, a.CREATED_WHEN"
                         + ", CONCAT(a.START_DATE, CASE a.START_TIME WHEN 'M' THEN ' (เช้า)'"
                         + " WHEN 'A' THEN ' (บ่าย)'"
                         + " ELSE '' END) AS START_DESCR"
                         + ", CONCAT(a.END_DATE, CASE a.END_TIME WHEN 'M' THEN ' (เช้า)'"
                         + " WHEN 'A' THEN ' (บ่าย)'"
                         + " ELSE '' END) AS END_DESCR"
                         + " FROM HRM_PS_ABSENCES a"
                         + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                         + (abInfo.Sequence > 0 ? " AND a.ABSENCE_SEQ = @Sequence" : "")
                         + (EmpSequence > 0 ? " AND a.EMP_SEQ = @EmpSequence" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    AbsenceTypeDAL atDAL = new AbsenceTypeDAL();
                    AbsenceTypeInfo atInfo = new AbsenceTypeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<AbsenceTypeInfo> atList = atDAL.ListAbsenceTypes(new AbsenceTypeInfo(rdr.GetInt32(1)));
                        atInfo = atList[0];
                    }

                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(2)));
                        empInfo = empList.Count > 0 ? empList[0] : null;
                    }

                    AbsenceInfo Info = new AbsenceInfo(rdr.GetInt32(0)
                                                       , atInfo, empInfo
                                                       , rdr.IsDBNull(3) ? (DateTime?) null : rdr.GetDateTime(3)
                                                       , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                                                       , rdr.IsDBNull(5) ? (DateTime?) null : rdr.GetDateTime(5)
                                                       , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                                                       , rdr.IsDBNull(7) ? 0 : rdr.GetDecimal(7)
                                                       , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                                                       , rdr.IsDBNull(9) ? "" : rdr.GetString(9)
                                                       , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                                                       , rdr.IsDBNull(11) ? "" : rdr.GetString(11)
                                                       , rdr.IsDBNull(12) ? (DateTime?) null : rdr.GetDateTime(12)
                                                       , rdr.IsDBNull(13) ? "" : rdr.GetString(13)
                                                       , rdr.IsDBNull(14) ? "" : rdr.GetString(14)
                                                       , rdr.IsDBNull(15) ? (int?) null : rdr.GetInt32(15)
                                                       , rdr.IsDBNull(16) ? (int?) null : rdr.GetInt32(16)
                        );
                    infoList.Add(Info);
                }

                return infoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }


        public IList<AbsenceInfo> TreeViewAbsences(AbsenceInfo abInfo)
        {
            string strSQL;
            int EmpSequence = 0;
            int JobSiteSequence = 0;
            if (abInfo.Employee != null)
                EmpSequence = abInfo.Employee.Sequence;

            List<AbsenceInfo> infoList = new List<AbsenceInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@JobSiteSequence", "%/" + JobSiteSequence.ToString() + "/%");
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);

                strSQL = "SELECT a.ABSENCE_SEQ, a.ABSENCE_TYPE_SEQ, a.EMP_SEQ, a.START_DATE, a.START_TIME"
                         + ", a.END_DATE, a.END_TIME, a.COUNT_DATE, a.CONTACT_ADDRESS"
                         + ", a.APPROVED_STATUS, a.APPROVED_BY, a.APPROVED_POSITION, a.APPROVED_DATE"
                         + ", a.REMARKS, a.RECORD_STATUS, a.CREATED_BY, a.UPDATED_BY, a.CREATED_WHEN"
                         + " FROM HRM_PS_ABSENCES a"
                         + " LEFT JOIN HRM_PS_EMPLOYEES e ON a.EMP_SEQ = e.EMP_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                         + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                         + (JobSiteSequence > 0 ? " AND j.FULL_PATH LIKE @JobSiteSequence" : "")
                         + (EmpSequence > 0 ? " AND a.EMP_SEQ = @EmpSequence" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    AbsenceInfo Info = new AbsenceInfo();
                    Info.Sequence = rdr.GetInt32(0);
                    Info.CreatedWhen = rdr.IsDBNull(17) ? (DateTime?) null : rdr.GetDateTime(17);
                    infoList.Add(Info);
                }

                return infoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataSet LoadAbsences(AbsenceInfo abInfo)
        {
            string strSQL;
            int EmpSequence = 0;
            if (abInfo.Employee != null)
                EmpSequence = abInfo.Employee.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", abInfo.Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);

                strSQL = "SELECT a.ABSENCE_SEQ, a.ABSENCE_TYPE_SEQ, a.EMP_SEQ, a.START_DATE, a.START_TIME"
                         + ", a.END_DATE, a.END_TIME, a.COUNT_DATE, a.CONTACT_ADDRESS"
                         + ", a.APPROVED_STATUS, a.APPROVED_BY, a.APPROVED_POSITION, a.APPROVED_DATE"
                         + ", a.REMARKS, a.RECORD_STATUS, a.CREATED_BY, a.UPDATED_BY, a.CREATED_WHEN"
                         + ", CONCAT(a.START_DATE, CASE a.START_TIME WHEN 'M' THEN ' (เช้า)'"
                         + " WHEN 'A' THEN ' (บ่าย)'"
                         + " ELSE '' END) AS START_DESCR"
                         + ", CONCAT(a.END_DATE, CASE a.END_TIME WHEN 'M' THEN ' (เช้า)'"
                         + " WHEN 'A' THEN ' (บ่าย)'"
                         + " ELSE '' END) AS END_DESCR"
                         + ", at.ABSENCE_TYPE_NAME"
                         + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                         + ", CONCAT(npp.PREFIX_NAME, ap.FIRST_NAME, '  ', ap.LAST_NAME) AS APPROVED_NAME"
                         + " FROM HRM_PS_ABSENCES a"
                         + " LEFT JOIN HRM_PS_ABSENCE_TYPES at ON a.ABSENCE_TYPE_SEQ = at.ABSENCE_TYPE_SEQ"
                         + " LEFT JOIN HRM_PS_EMPLOYEES e ON a.EMP_SEQ = e.EMP_SEQ"
                         + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                         + " LEFT JOIN HRM_PS_EMPLOYEES ap ON a.APPROVED_BY = ap.EMP_SEQ"
                         + " LEFT JOIN MAS_NAME_PREFIXES npp ON ap.NAME_PREFIX_SEQ = npp.NAME_PREFIX_SEQ"
                         + " WHERE a.RECORD_STATUS = @ActiveRecord"
                         + (abInfo.Sequence > 0 ? " AND a.ABSENCE_SEQ = @Sequence" : "")
                         + (EmpSequence > 0 ? " AND a.EMP_SEQ = @EmpSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Insert(AbsenceInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@AbsenceTypeSeq", Info.AbsenceType == null ? (int?) null : Info.AbsenceType.Sequence);
                dbManager.AddParameters(1, "@EmpSeq", Info.Employee == null ? (int?) null : Info.Employee.Sequence);
                dbManager.AddParameters(2, "@StartTime", Info.StartTime);
                dbManager.AddParameters(3, "@EndTime", Info.EndTime);
                dbManager.AddParameters(4, "@StartDate", Info.StartDate);
                dbManager.AddParameters(5, "@EndDate", Info.EndDate);
                dbManager.AddParameters(6, "@CountDay", Info.CountDate);
                dbManager.AddParameters(7, "@ContactAddress", Info.ContactAddress);
                dbManager.AddParameters(8, "@AppStatus", Info.Status);
                dbManager.AddParameters(9, "@AppBy", Info.ApprovedBy);
                dbManager.AddParameters(10, "@AppPosition", Info.ApprovedPosition);
                dbManager.AddParameters(11, "@AppDate", Info.ApprovedDate);
                dbManager.AddParameters(12, "@Remarks", Info.Remarks);
                dbManager.AddParameters(13, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(15, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_ABSENCES(ABSENCE_TYPE_SEQ, EMP_SEQ, START_TIME"
                         + ", END_TIME, START_DATE, END_DATE, COUNT_DATE, CONTACT_ADDRESS"
                         + ", APPROVED_STATUS, APPROVED_BY, APPROVED_POSITION, APPROVED_DATE"
                         + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                         + " VALUES(@AbsenceTypeSeq, @EmpSeq, @StartTime, @EndTime, @StartDate, @EndDate"
                         + ", @CountDay, @ContactAddress, @AppStatus, @AppBy, @AppPosition, @AppDate"
                         + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(AbsenceInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@AbsenceTypeSeq", Info.AbsenceType == null ? (int?) null : Info.AbsenceType.Sequence);
                dbManager.AddParameters(2, "@EmpSeq", Info.Employee == null ? (int?) null : Info.Employee.Sequence);
                dbManager.AddParameters(3, "@StartTime", Info.StartTime);
                dbManager.AddParameters(4, "@EndTime", Info.EndTime);
                dbManager.AddParameters(5, "@StartDate", Info.StartDate);
                dbManager.AddParameters(6, "@EndDate", Info.EndDate);
                dbManager.AddParameters(7, "@CountDay", Info.CountDate);
                dbManager.AddParameters(8, "@ContactAddress", Info.ContactAddress);
                dbManager.AddParameters(9, "@AppStatus", Info.Status);
                dbManager.AddParameters(10, "@AppBy", Info.ApprovedBy);
                dbManager.AddParameters(11, "@AppPosition", Info.ApprovedPosition);
                dbManager.AddParameters(12, "@AppDate", Info.ApprovedDate);
                dbManager.AddParameters(13, "@Remarks", Info.Remarks);
                dbManager.AddParameters(14, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(15, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCES"
                         + " SET ABSENCE_TYPE_SEQ = @AbsenceTypeSeq"
                         + ", EMP_SEQ = @EmpSeq"
                         + ", START_DATE = @StartDate"
                         + ", START_TIME = @StartTime"
                         + ", END_DATE = @EndDate"
                         + ", END_TIME = @EndTime"
                         + ", COUNT_DATE = @CountDay"
                         + ", CONTACT_ADDRESS = @ContactAddress"
                         + ", APPROVED_STATUS = @AppStatus"
                         + ", APPROVED_BY = @AppBy"
                         + ", APPROVED_POSITION = @AppPosition"
                         + ", APPROVED_DATE = @AppDate"
                         + ", REMARKS = @Remarks"
                         + ", UPDATED_BY = @UpdatedBy"
                         + ", UPDATED_WHEN = @UpdatedWhen"
                         + " WHERE ABSENCE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCES"
                         + " SET RECORD_STATUS = @RecordStatus"
                         + ", UPDATED_BY = @UpdatedBy"
                         + ", UPDATED_WHEN = @UpdatedWhen"
                         + " WHERE ABSENCE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataSet GetEmployeeNameList(int EmpSequence)
        {
            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);

                strSQL = "SELECT e.EMP_SEQ, CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                         + " FROM HRM_PS_EMPLOYEES e"
                         + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                         + " WHERE e.RECORD_STATUS = @ActiveRecord"
                         + (EmpSequence > 0 ? " AND e.EMP_SEQ = @EmpSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        //for service
        public IList<hrm_ps_absence> GetAbsenceDays(string emp_seq)
        {
            string strSQL;
            try
            {
                List<hrm_ps_absence> result = new List<hrm_ps_absence>();
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                //dbManager.AddParameters(2, "@WorkingDate", working_date);

                //strSQL = "SELECT absence_seq,absence_type_seq,emp_seq,start_date,start_time,end_date,end_time"
                strSQL = "SELECT *"
                         + " FROM HRM_PS_ABSENCE"
                         + " WHERE RECORD_STATUS = @RecordStatus";
                if (!String.IsNullOrEmpty(emp_seq)) strSQL += " AND emp_seq = @EmpSeq";
                //if (!String.IsNullOrEmpty(working_date)) strSQL += " AND (start_date <= @WorkingDate AND end_date >= @WorkingDate)";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<hrm_ps_absence>) CollectionHelper.ConvertTo<hrm_ps_absence>(dtb);

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }
    }
}